﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;

using Microsoft.VisualStudio.TestTools.UnitTesting;

using PublicDomain.Data.Csv;
using PublicDomain.Framework.UnitTesting;

namespace PublicDomain.UnitTest.Data.Csv
{
    [TestClass]
    public class SqlBulkCopyAdapterTest
    {
        private static string sqlServerTestDatabaseConnection;

        private SqlConnection destinationConnection;
        private SqlBulkCopy sqlBulkCopy;
        private CsvCommand csvCommand;

        private int previousCsvDataRowIndex = 1;
        private DataRow previousDataRow;

        public TestContext TestContext { get; set; }

        [ClassInitialize]
        public static void ClassInitialize(TestContext context)
        {
            sqlServerTestDatabaseConnection = ConfigurationManager.ConnectionStrings["SqlServerTestDatabaseConnection"].ConnectionString;
        }

        [TestInitialize]
        public void TestInitialize()
        {
            this.destinationConnection = new SqlConnection(sqlServerTestDatabaseConnection);
            this.destinationConnection.Open();

            var tableName = this.GetTestMethodTableName();
            DropDestinationTable(tableName);
            CreateDestinationTable(tableName);

            this.sqlBulkCopy = new SqlBulkCopy(this.destinationConnection);
            this.sqlBulkCopy.DestinationTableName = tableName;

            var schemataFilePath = Path.Combine(this.TestContext.TestDeploymentDir, (string)this.TestContext.Properties["SchemataFileName"]);
            var connectionString = String.Format("Database={0};SchemataFilePath={1}", this.TestContext.TestDeploymentDir, schemataFilePath);

            this.csvCommand = new CsvCommand((string)this.TestContext.Properties["CsvInputFileName"], new CsvConnection(connectionString));
        }

        [TestCleanup]
        public void TestCleanup()
        {
            DropDestinationTable(this.GetTestMethodTableName());

            this.csvCommand.Dispose();
            this.sqlBulkCopy.Close();
            this.destinationConnection.Dispose();
        }

        [TestMethod]
        [Description("Tests method WriteToServer for the basic case without event handlers.")]
        [DeploymentItem("SqlBulkCopyAdapterTestInput.csv")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputSchemata.xml")]
        [TestProperty("CsvInputFileName", "SqlBulkCopyAdapterTestInput.csv")]
        [TestProperty("SchemataFileName", "SqlBulkCopyAdapterTestInputSchemata.xml")]
        public void WriteToServerTest()
        {
            var dataReader = (CsvDataReader)this.csvCommand.ExecuteReader();

            this.sqlBulkCopy.WriteToServer(dataReader, 3, null, null);

            var recordCount = GetDestinationTableRecordCount(this.GetTestMethodTableName());
            Assert.AreEqual(4, recordCount);
        }

        [TestMethod]
        [Description("Tests method WriteToServer in the case of parameter sqlBulkCopy is null.")]
        [DeploymentItem("SqlBulkCopyAdapterTestInput.csv")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputSchemata.xml")]
        [TestProperty("CsvInputFileName", "SqlBulkCopyAdapterTestInput.csv")]
        [TestProperty("SchemataFileName", "SqlBulkCopyAdapterTestInputSchemata.xml")]
        public void WriteToServerSqlBulkCopyArgumentNullExceptionTest()
        {
            var dataReader = (CsvDataReader)this.csvCommand.ExecuteReader();

            AssertExtensions.ThrowsArgumentNullException(
                () => SqlBulkCopyAdapter.WriteToServer(null, dataReader, 3, null, null),
                "sqlBulkCopy");
        }

        [TestMethod]
        [Description("Tests method WriteToServer in the case of parameter dataReader is null.")]
        [DeploymentItem("SqlBulkCopyAdapterTestInput.csv")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputSchemata.xml")]
        [TestProperty("CsvInputFileName", "SqlBulkCopyAdapterTestInput.csv")]
        [TestProperty("SchemataFileName", "SqlBulkCopyAdapterTestInputSchemata.xml")]
        public void WriteToServerDataReaderArgumentNullExceptionTest()
        {
            var dataReader = (CsvDataReader)this.csvCommand.ExecuteReader();

            AssertExtensions.ThrowsArgumentNullException(
                () => SqlBulkCopyAdapter.WriteToServer(this.sqlBulkCopy, null, 3, null, null),
                "dataReader");
        }

        [TestMethod]
        [Description("Tests read data row error handler without re-throw of the method WriteToServer.")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputErrorHandler.csv")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputSchemata.xml")]
        [TestProperty("CsvInputFileName", "SqlBulkCopyAdapterTestInputErrorHandler.csv")]
        [TestProperty("SchemataFileName", "SqlBulkCopyAdapterTestInputSchemata.xml")]
        public void WriteToServerReadDataRowErrorHandlerNoRethrowTest()
        {
            var dataReader = (CsvDataReader)this.csvCommand.ExecuteReader();

            SqlBulkCopyAdapter.WriteToServer(this.sqlBulkCopy, dataReader, 3, null, this.WriteToServerReadDataRowErrorHandlerNoRethrow);

            var recordCount = GetDestinationTableRecordCount(this.GetTestMethodTableName());
            Assert.AreEqual(3, recordCount);
        }

        [TestMethod]
        [Description("Tests read data row error handler with re-throw of the method WriteToServer.")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputErrorHandler.csv")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputSchemata.xml")]
        [TestProperty("CsvInputFileName", "SqlBulkCopyAdapterTestInputErrorHandler.csv")]
        [TestProperty("SchemataFileName", "SqlBulkCopyAdapterTestInputSchemata.xml")]
        public void WriteToServerReadDataRowErrorHandlerRethrowTest()
        {
            var dataReader = (CsvDataReader)this.csvCommand.ExecuteReader();

            AssertExtensions.Throws<CsvDataRowException>(
                () => SqlBulkCopyAdapter.WriteToServer(this.sqlBulkCopy, dataReader, 3, null, this.WriteToServerReadDataRowErrorHandlerRethrow),
                "The text '4...4' cannot be parsed as double.");
        }

        [TestMethod]
        [Description("Tests rethrow error in the method WriteToServer in the case of empty error handler .")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputErrorHandler.csv")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputSchemata.xml")]
        [TestProperty("CsvInputFileName", "SqlBulkCopyAdapterTestInputErrorHandler.csv")]
        [TestProperty("SchemataFileName", "SqlBulkCopyAdapterTestInputSchemata.xml")]
        public void WriteToServerNoReadDataRowErrorHandlerRethrowTest()
        {
            var dataReader = (CsvDataReader)this.csvCommand.ExecuteReader();

            AssertExtensions.Throws<CsvDataRowException>(
                () => SqlBulkCopyAdapter.WriteToServer(this.sqlBulkCopy, dataReader, 3, null, null),
                "The text '4...4' cannot be parsed as double.");
        }

        [TestMethod]
        [Description("Tests read data row handler of the method WriteToServer.")]
        [DeploymentItem("SqlBulkCopyAdapterTestInput.csv")]
        [DeploymentItem("SqlBulkCopyAdapterTestInputSchemata.xml")]
        [TestProperty("CsvInputFileName", "SqlBulkCopyAdapterTestInput.csv")]
        [TestProperty("SchemataFileName", "SqlBulkCopyAdapterTestInputSchemata.xml")]
        public void WriteToServerReadDataRowHandlerTest()
        {
            var dataReader = (CsvDataReader)this.csvCommand.ExecuteReader();

            SqlBulkCopyAdapter.WriteToServer(this.sqlBulkCopy, dataReader, 3, this.WriteToServerReadDataRowHandler, null);

            var recordCount = GetDestinationTableRecordCount(this.GetTestMethodTableName());
            Assert.AreEqual(4, recordCount);
        }

        private static void DropDestinationTable(string tableName)
        {
            using (var connection = new SqlConnection(sqlServerTestDatabaseConnection))
            {
                connection.Open();

                using (var dropDestinationTableCommand = connection.CreateCommand())
                {
                    dropDestinationTableCommand.CommandText =
                        String.Format(
                            CultureInfo.CurrentCulture,
                            "IF OBJECT_ID('[dbo].[{0}]', 'U') IS NOT NULL DROP TABLE [dbo].[{0}]",
                            tableName);
                    dropDestinationTableCommand.ExecuteNonQuery();
                }
            }
        }

        private static void CreateDestinationTable(string tableName)
        {
            using (var connection = new SqlConnection(sqlServerTestDatabaseConnection))
            {
                connection.Open();

                using (var createDestinationTableCommand = connection.CreateCommand())
                {
                    const string commandTextFormatString = 
                        "CREATE TABLE [dbo].[{0}]( " +
                            "[ColumnInt01] [int] NULL, " +
                            "[ColumnString02] [nvarchar](max) NULL, " +
                            "[ColumnDouble03] [float] NULL " +
                        ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";

                    createDestinationTableCommand.CommandText =
                        String.Format(
                            CultureInfo.CurrentCulture,
                            commandTextFormatString,
                            tableName);

                    createDestinationTableCommand.ExecuteNonQuery();
                }
            }
        }

        private static int GetDestinationTableRecordCount(string tableName)
        {
            using (var connection = new SqlConnection(sqlServerTestDatabaseConnection))
            {
                connection.Open();

                using (var dropDestinationTableCommand = connection.CreateCommand())
                {
                    dropDestinationTableCommand.CommandText =
                        String.Format(
                            CultureInfo.CurrentCulture,
                            "SELECT COUNT(*) FROM [dbo].[{0}]",
                            tableName);

                    var result = (int)dropDestinationTableCommand.ExecuteScalar();

                    return result;
                }
            }
        }

        private bool WriteToServerReadDataRowErrorHandlerNoRethrow(Exception exception, int csvDataRowIndex)
        {
            Assert.AreEqual(5, csvDataRowIndex);

            Assert.IsInstanceOfType(exception, typeof(CsvDataRowException));
            Assert.AreEqual("The text '4...4' cannot be parsed as double.", exception.Message);

            var result = false;

            return result;
        }

        private bool WriteToServerReadDataRowErrorHandlerRethrow(Exception exception, int csvDataRowIndex)
        {
            Assert.AreEqual(5, csvDataRowIndex);

            Assert.IsInstanceOfType(exception, typeof(CsvDataRowException));
            Assert.AreEqual("The text '4...4' cannot be parsed as double.", exception.Message);

            var result = true;

            return result;
        }

        private void WriteToServerReadDataRowHandler(DataRow dataRow, int csvDataRowIndex)
        {
            Assert.IsTrue((csvDataRowIndex - this.previousCsvDataRowIndex) == 1);
            this.previousCsvDataRowIndex = csvDataRowIndex;

            Assert.AreNotSame(this.previousDataRow, dataRow);
            this.previousDataRow = dataRow;
        }

        private string GetTestMethodTableName()
        {
            var result = String.Concat(this.TestContext.TestName, "Table");

            return result;
        }
    }
}
